package com.eorchis.module.dataSynchronization.dao.impl;

import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import com.eorchis.constants.DeptConstants;
import com.eorchis.constants.SystemConstants;
import com.eorchis.core.basedao.dao.impl.HibernateAbstractBaseDao;
import com.eorchis.module.dataSynchronization.bean.ClassScopeDept;
import com.eorchis.module.dataSynchronization.bean.ClassSignUpDept;
import com.eorchis.module.dataSynchronization.bean.Department;
import com.eorchis.module.dataSynchronization.bean.DepartmentUser;
import com.eorchis.module.dataSynchronization.bean.TrainingClass;
import com.eorchis.module.dataSynchronization.bean.TrainingClassUser;
import com.eorchis.module.dataSynchronization.bean.User;
import com.eorchis.module.dataSynchronization.bean.UserClassTypeHour;
import com.eorchis.module.dataSynchronization.dao.ISynchroDao;
import com.eorchis.module.trainingclass.dao.ITrainingClassDao;
import com.eorchis.utils.utils.PropertyUtil;

@SuppressWarnings("unchecked")
@Repository(value = "com.eorchis.module.dataSynchronization.dao.impl.synchroDaoImpl")
public class synchroDaoImpl extends HibernateAbstractBaseDao implements ISynchroDao{

	@Autowired
	@Qualifier("com.eorchis.module.trainingclass.dao.impl.TrainingClassDaoImpl")
	private ITrainingClassDao trainingClassDao;
	
	@Override
	public void addDepartment(Department dept) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("insert into " + SystemConstants.SYSTEM_CODE + "uum_department ");
			// params.put("deptId", UUID.randomUUID().toString().replace("-", ""));
			sb.append(consParam4Insert(params, new String[][] {
				{"depid", dept.getDepID()},
				{"parent_id", dept.getParentID()},
				{"depname", dept.getDepName()},
				{"depcode", dept.getDepCode()},
				{"order_num", dept.getOrderNum().toString()},
				{"treepath", dept.getTreepath()},
				{"active_state", dept.getActiveState().toString()},
				{"last_operate_time", dept.getLastOperateTime()},
				{"report_depid", dept.getReportDepid()},
				{"admin_level_code", dept.getAdminLevelCode()}
				}));
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updateDepartment(Department dept) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update " + SystemConstants.SYSTEM_CODE + "uum_department set ");
			sb.append(consParam4Update(params, new String[][] {
				{"parent_id", dept.getParentID()},
				{"depname", dept.getDepName()},
				{"depcode", dept.getDepCode()},
				{"order_num", dept.getOrderNum().toString()},
				{"treepath", dept.getTreepath()},
				{"active_state", dept.getActiveState().toString()},
				{"last_operate_time", dept.getLastOperateTime()},
				{"report_depid", dept.getReportDepid()},
				{"admin_level_code", dept.getAdminLevelCode()}
				}));
			sb.append(" where depid = :depID");
			params.put("depID", dept.getDepID());
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public int addUser(User user) {
		try {
			StringBuffer sb = new StringBuffer("insert into " + SystemConstants.SYSTEM_CODE + "uum_user ");
			Map<String, Object> params = new HashMap<String, Object>();
			// String id=UUID.randomUUID().toString().replaceAll("-", "");
			// params.put("password", "cf79ae6addba60ad018347359bd144d2"); //默认密码8888
			sb.append(consParam4Insert(params, new String[][] {
				{"userid", user.getUserID()},
				{"login_id", user.getLoginID()},
				{"password", user.getPassword()},
				{"user_name", user.getUserName()},
				{"sex_code", user.getSexCode()},
				{"birthday", user.getBirthday()},
				{"mobile_telephone", user.getMobileTelephone()},
				{"create_date", user.getCreateDate()},
				{"folk_code", user.getFolkCode()},
				{"paper_type_code", user.getPaperTypeCode()},
//				{"paper_num", user.getPaperNum()},
				{"politices_code", user.getPoliticesCode()},
				{"degree_code", user.getDegreeCode()},
				{"duty_level_code", user.getDutyLevelCode()},
				{"duty_remark", user.getDutyRemark()},
				{"zugong", user.getZugong().toString()},
				{"exam_state", user.getExamState().toString()},
				{"chisel_state", user.getChiselState().toString()},
				{"keydisk_serialnum", user.getKeydiskSerialnum()},
				{"admin_state", user.getAdminState().toString()},
				{"student_state", user.getStudentState().toString()},
				{"teacher_state", user.getTeacherState().toString()},
				{"active_state", user.getActiveState().toString()},
				{"order_num", user.getOrderNum().toString()},
				{"availability_state", user.getAvailabilityState().toString()},
				{"shiguan", user.getShiguan().toString()},
				{"view_state", user.getViewState().toString()},
				{"officeholding_date", user.getOfficeHoldingDate()},
				{"last_operate_time", user.getLastOperateTime()},
				}));
			return this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}
	
	public String consParam4Insert(Map<String, Object> params, String[][] field) {
		StringBuffer fieldSql = new StringBuffer();
		StringBuffer valueSql = new StringBuffer();
		for (String[] f : field) {
			if (PropertyUtil.objectNotEmpty(f[1])) {
				fieldSql.append("," + f[0]);
				valueSql.append(",:" + f[0]);
				params.put(f[0], f[1]);
			}
		}
		return "(" + fieldSql.substring(1) + ") values (" + valueSql.substring(1) + ")";
	}
	
	public String consParam4Update(Map<String, Object> params, String[][] field) {
		StringBuffer sql = new StringBuffer();
		for (String[] f : field) {
			if (PropertyUtil.objectNotEmpty(f[1])) {
				sql.append("," + f[0] + "=:" + f[0]);
				params.put(f[0], f[1]);
			} else
				sql.append("," + f[0] + " = null");
		}
		return sql.substring(1);
	}
	
	@Override
	public int updateUser(User user) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update " + SystemConstants.SYSTEM_CODE + "uum_user set ");
			sb.append(consParam4Update(params, new String[][] {
				{"login_id", user.getLoginID()},
				{"password", user.getPassword()},
				{"user_name", user.getUserName()},
				{"sex_code", user.getSexCode()},
				{"birthday", user.getBirthday()},
				{"mobile_telephone", user.getMobileTelephone()},
				{"create_date", user.getCreateDate()},
				{"folk_code", user.getFolkCode()},
				{"paper_type_code", user.getPaperTypeCode()},
//				{"paper_num", user.getPaperNum()},
				{"politices_code", user.getPoliticesCode()},
				{"degree_code", user.getDegreeCode()},
				{"duty_level_code", user.getDutyLevelCode()},
				{"duty_remark", user.getDutyRemark()},
				{"zugong", user.getZugong().toString()},
				{"exam_state", user.getExamState().toString()},
				{"chisel_state", user.getChiselState().toString()},
				{"keydisk_serialnum", user.getKeydiskSerialnum()},
				{"admin_state", user.getAdminState().toString()},
				{"student_state", user.getStudentState().toString()},
				{"teacher_state", user.getTeacherState().toString()},
				{"active_state", user.getActiveState().toString()},
				{"order_num", user.getOrderNum().toString()},
				{"availability_state", user.getAvailabilityState().toString()},
				{"shiguan", user.getShiguan().toString()},
				{"view_state", user.getViewState().toString()},
//				{"officeholding_date", user.getOfficeHoldingDate()},
				{"last_operate_time", user.getLastOperateTime()}
				}));
			sb.append(" where userid=:userID");
			params.put("userID", user.getUserID());
			return this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return 0;
	}

	@Override
	public String[] getApplicationKey(String code){
		String s="select t.system_parameter_value from BASE_SYSTEM_PARAMETER t where t.system_parameter_name=:key";
		Map<String,Object> params=new HashMap<String,Object>();
		params.put("key", "DEFAULT_PASSWORD");
		List<String> list=(List<String>) this.executeFind(QueryStringType.SQL, s, params);
		String []ss=new String[2];
		if(PropertyUtil.objectNotEmpty(list)){
			for(int i =0;i<list.size();i++){
				ss[i]=list.get(i);
			}
			return ss;
		}
		return null;
	}
	
	//判断部门与人员是否绑定
	public boolean judgeDepartmentUser(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from " + SystemConstants.SYSTEM_CODE + "uum_department_user du where du.depuser_id = :depUserId";
			params.put("depUserId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public void updateDepartmentUser(DepartmentUser deptUser) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sql = new StringBuffer("update " + SystemConstants.SYSTEM_CODE + "uum_department_user set ");
			sql.append(consParam4Update(params, new String[][] {
				{"userid", deptUser.getUserID()},
				{"depid", deptUser.getDepID()},
				{"active_state", deptUser.getActiveState().toString()},
				{"order_num", deptUser.getOrderNum().toString()},
				{"last_operate_time", deptUser.getLastOperateTime()}
			}));
			sql.append(" where depuser_id=:depuserID");
			params.put("depuserID", deptUser.getDepuserID());
			this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public void addDepartmentUser(DepartmentUser deptUser) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sql = new StringBuffer("insert into " + SystemConstants.SYSTEM_CODE + "uum_department_user ");
			// params.put("bduId", UUID.randomUUID().toString().replaceAll("-", ""));
			sql.append(consParam4Insert(params, new String[][] {
				{"depuser_id", deptUser.getDepuserID()},
				{"userid", deptUser.getUserID()},
				{"depid", deptUser.getDepID()},
				{"active_state", deptUser.getActiveState().toString()},
				{"order_num", deptUser.getOrderNum().toString()},
				{"last_operate_time", deptUser.getLastOperateTime()}
			}));
			this.executeUpdate(QueryStringType.SQL, sql.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public boolean judgeDepartment(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = " select count(1) from " + SystemConstants.SYSTEM_CODE + "uum_department d where d.depid = :deptId ";
			params.put("deptId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public String getDepartmentId(String ...strings){
		try{
			Map<String,Object> params=new HashMap<String,Object>();
			String sql=" select depid from " + SystemConstants.SYSTEM_CODE + "uum_department d where d.depid=:deptId ";
			params.put("deptId", strings[0]);
			List<?> list=this.executeFind(QueryStringType.SQL, sql, params);
			if(PropertyUtil.objectNotEmpty(list)&&PropertyUtil.objectNotEmpty(list.get(0))){
				return list.get(0).toString();
			}
		}catch(Exception e){
			e.printStackTrace();
			System.out.println("部门ID："+strings[0]);
		}
		return null;
	}
	
	public boolean judgeUser(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from " + SystemConstants.SYSTEM_CODE + "uum_user u where u.userid = :userId";
			params.put("userId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public List<User> findUserList(Integer daysBefore) {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer("select cast(bu.userid as char) \"userID\",cast(bu.login_id as char) \"loginID\",bu.password \"password\",bu.user_name \"userName\","
				+ "cast(bu.sex_code as char) \"sexCode\",date_format(bu.birthday,'%Y%m%d') \"birthday\",bu.mobile_telephone \"mobileTelephone\","
				+ "cast(bu.folk_code as char) \"folkCode\",cast(bu.paper_type_code as char) \"paperTypeCode\",bu.paper_num \"paperNum\",cast(bu.politices_code as char) \"politicesCode\","
				+ "cast(bu.degree_code as char) \"degreeCode\",cast(bu.duty_level_code as char) \"dutyLevelCode\",bu.duty_remark \"dutyRemark\",ifnull(bu.zugong,0) \"zugong\","
				+ "ifnull(bu.exam_state,0) \"examState\",bu.chisel_State \"chiselState\",bu.keydisk_Serialnum \"keydiskSerialnum\",ifnull(bu.admin_state,0) \"adminState\","
				+ "ifnull(bu.student_state,0) \"studentState\",ifnull(bu.teacher_state,0) \"teacherState\",bu.active_state \"activeState\",ifnull(bu.order_num,0) \"orderNum\","
				+ "bu.availability_State \"availabilityState\",bu.shiguan \"shiguan\",ifnull(bu.view_state,0) \"viewState\",date_format(bu.last_operate_time,'%Y%m%d') \"lastOperateTime\","
				+ "date_format(bu.officeHolding_Date,'%Y%m%d') \"officeHoldingDate\",date_format(bu.create_date,'%Y%m%d') \"createDate\" "
				+ "from " + SystemConstants.SYSTEM_CODE + "uum_user bu");
		if (PropertyUtil.objectNotEmpty(daysBefore)) {
			sb.append(" where bu.last_operate_time >= date_format(date_add(now(),interval -:daysBefore day),'%y%m%d')"); 
			params.put("daysBefore", daysBefore);
		}
		return (List<User>) this.executeFind(QueryStringType.SQL, sb.toString(), params,User.class);
	}

	@Override
	public List<TrainingClassUser> findTrainingClassUserList(Integer daysBefore) {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer(" select tcu.class_user_id \"classUserID\", tcu.class_id \"classID\", tcu.active_state  \"activeState\",  tcu.user_id  \"userID\","
				+ " ifnull(tcu.auditing_state,1) \"auditingState\", tcu.attendance_state \"attendanceState\",  tcu.sign_up_type  \"signUpType\", tcu.user_name  \"userName\","
				+ " tcu.sex_code \"sexCode\", date_format(tcu.birthday,'%Y%m%d') \"birthday\", tcu.mobile_telephone \"mobileTelephone\", tcu.folk_code  \"folkCode\", tcu.duty_remark \"dutyRemark\","
				+ " tcu.remark   \"remark\", tcu.reject_reason \"rejectReason\",  ifnull(cast(tcu.training_hours as char),cast(tc.training_hour as char)) \"trainingHours\",date_format(tcu.latest_operate_date,'%Y%m%d') \"latestOperateDate\""
				+ " from training_class_user tcu left join training_class tc on tcu.class_id = tc.class_id");
		if (PropertyUtil.objectNotEmpty(daysBefore)) {
			sb.append(" where tcu.latest_operate_date >= date_format(date_add(now(),interval -:daysBefore day),'%y%m%d')"); 
			params.put("daysBefore", daysBefore);
		}
		return (List<TrainingClassUser>)this.executeFind(QueryStringType.SQL, sb.toString(), params,TrainingClassUser.class);
	}

	@Override
	public List<TrainingClass> findTrainingClassList(Integer daysBefore) {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer(" select tc.class_id \"classID\", tc.class_name \"className\", tc.classname_year \"classnameYear\", "
				+ "tc.class_main_name \"classMainName\", tc.class_num \"classNum\", tc.class_type \"classType\", cast(tc.training_hour as char) \"trainingHour\", "
				+ "date_format(tc.begin_date,'%Y%m%d') \"beginDate\", date_format(tc.end_date,'%Y%m%d') \"endDate\", date_format(tc.enter_begin_time,'%Y%m%d') \"enterBeginTime\",  date_format(tc.enter_end_time,'%Y%m%d') \"enterEndTime\", "
				+ "tc.training_dept_name \"trainingDeptName\", tc.sponsor_id \"sponsorID\",  if(tc.sponsor_name='西城区委组织部','西城区',tc.sponsor_name) \"sponsorName\", ifnull(tc.publish_state,0) \"publishState\", "
				+ "ifnull(tc.open_state,0) \"openState\", tc.active_state \"activeState\", ifnull(tc.report_state,0) \"reportState\", ifnull(tc.audit_state,0) \"auditState\",date_format(tc.latest_operate_date,'%Y%m%d') \"latestOperateDate\", "
				+ "date_format(tc.create_date,'%Y%m%d') \"createDate\", ifnull(tc.signup_state,0) \"signupState\", ifnull(tc.perfect_state,0) \"perfectState\",  ifnull(tc.allotdept_state,0) \"allotdeptState\", "
				+ "date_format(tc.assess_start_time,'%Y%m%d') \"assessStartTime\", date_format(tc.assess_end_time,'%Y%m%d') \"assessEndTime\", tc.lock_state \"lockState\",  tc.category_id \"categoryID\", "
				+ "tc.beoff_station \"beoffStation\", (select data_name from base_basedata bd where bd.data_code=tc.beoff_station) \"beoffStationName\", cast(tc.class_user_num as char) \"classUserNum\", "
				+ "tc.training_address \"trainingAddress\", tc.training_dept_id \"trainingDeptId\", tc.apply_way \"applyWay\", tc.class_description \"classDescription\", "
				+ "tc.fax \"fax\", tc.contact_tel \"contactTel\", tc.contact_people \"contactPeople\", tc.phone \"phone\" "
				+ "from training_class tc where 1=1 ");
		if (PropertyUtil.objectNotEmpty(daysBefore)) {
			sb.append(" and tc.latest_operate_date >= date_format(date_add(now(),interval -:daysBefore day),'%y%m%d')"); 
			params.put("daysBefore", daysBefore);
		}
		sb.append(" and tc.sponsor_id in (select depid from "+SystemConstants.SYSTEM_CODE+"uum_department u where u.treepath like :treePath) ");
		params.put("treePath", DeptConstants.XCH_DEPT_PATH+"%");
		return (List<TrainingClass>) this.executeFind(QueryStringType.SQL, sb.toString(), params,TrainingClass.class);
	}
	
	@Override
	public List<ClassScopeDept> findClassScopeDeptList(String[] classIds) {
		if (PropertyUtil.objectNotEmpty(classIds) && classIds.length > 0) {
			Map<String,Object> params=new HashMap<String,Object>();
			StringBuffer sb=new StringBuffer(" select t.scope_dept_id \"scopeDeptId\", t.class_id \"classId\", t.dept_id \"deptId\","
					+ " t.active_state \"activeState\", date_format(t.create_date,'%Y%m%d') \"createDate\" from class_scope_department t");
			sb.append(" where t.class_id in (:classIds)"); 
			params.put("classIds", classIds);
			return (List<ClassScopeDept>) this.executeFind(QueryStringType.SQL, sb.toString(), params,ClassScopeDept.class);
		}
		return null;
	}
	
	@Override
	public List<ClassSignUpDept> findClassSignUpDeptList(String[] classIds) {
		if (PropertyUtil.objectNotEmpty(classIds) && classIds.length > 0) {
			Map<String,Object> params=new HashMap<String,Object>();
			StringBuffer sb=new StringBuffer(" select t.sign_up_dept_id \"signUpDeptId\", t.class_id \"classId\", t.dept_id \"deptId\","
					+ " t.active_state \"activeState\", date_format(t.create_date,'%Y%m%d') \"createDate\" from class_sign_up_department t");
			sb.append(" where t.class_id in (:classIds)"); 
			params.put("classIds", classIds);
			return (List<ClassSignUpDept>) this.executeFind(QueryStringType.SQL, sb.toString(), params,ClassSignUpDept.class);
		}
		return null;
	}

	@Override
	public Department findDepartment(Department d) {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer("select to_char(bd.depid) \"depID\",to_char(bd.parent_id) \"parentID\",bd.depname \"depName\",bd.depcode \"depCode\","
				+ "nvl(bd.order_num,0) \"orderNum\",bd.treepath \"treepath\",bd.active_state \"activeState\",to_char(bd.last_operate_time,'yyyy-MM-dd') \"lastOperateTime\","
				+ "to_char(bd.report_depid) \"reportDepid\",nvl(bd.admin_level_code,0) \"adminLevelCode\" from uum_user@uum_link bu  "
				+ "left join uum_department_user@uum_link bdu on bdu.userid=bu.userid left join uum_department@uum_link bd on bdu.depid=bd.depid "
				+ "where bd.treepath like :path ");
//		sb.append("and bd.last_operate_time>=to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')"); 
		params.put("path", DeptConstants.XCH_DEPT_PATH+"%");
		List<Department> l=(List<Department>) this.executeFind(QueryStringType.SQL, sb.toString(), params,Department.class);
		if(PropertyUtil.objectNotEmpty(l))
			return l.get(0);
		else
			return null;
	}

	@Override
	public DepartmentUser findDepartmentUser(DepartmentUser d) {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer("select to_char(bdu.depuser_id) \"depuserID\",to_char(bdu.userid) \"userID\",to_char(bdu.depid) \"deptID\",bdu.active_state \"activeState\","
				+ "nvl(bdu.order_num,0) \"orderNum\",to_char(bdu.last_operate_time,'yyyy-MM-dd') \"lastOperateTime\" from uum_user@uum_link bu "
				+ "left join uum_department_user@uum_link bdu on bdu.userid=bu.userid "
				+ "left join uum_department@uum_link bd on bdu.depid=bd.depid "
				+ "where bd.treepath like :path ");
//		sb.append("and BDU.last_operate_time>=to_date(to_char(sysdate,'yyyy-MM-dd'),'yyyy-MM-dd')"); 
		params.put("path", DeptConstants.XCH_DEPT_PATH+"%");
		List<DepartmentUser> l=(List<DepartmentUser>) this.executeFind(QueryStringType.SQL, sb.toString(), params,DepartmentUser.class);
		if(PropertyUtil.objectNotEmpty(l))
			return l.get(0);
		else
			return null;
	}

	@Override
	public UserClassTypeHour findUserClassTypeHour(UserClassTypeHour u) {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer(" select t.id \"ID\", t.hour_year \"hourYear\", t.zx_hour \"zxHour\", t.zxzs_hour \"zxZsHour\", "
				+ "t.ztb_hour \"ztbHour\", t.zhutb_hour \"zhutbHour\", t.zhongydx_hour \"zhongydxHour\", t.jwpx_hour \"jwpxHour\", "
				+ "t.qit_hour \"qitHour\", t.hours \"hours\",  to_char(t.create_date,'yyyy-MM-dd') \"createDateStr\", t.user_id \"userID\" "
				+ " from user_class_type_hour t  where t.hour_year=:year ");
		params.put("year", new SimpleDateFormat("yyyy").format(new Date()));
		List<UserClassTypeHour> l=(List<UserClassTypeHour>) this.executeFind(QueryStringType.SQL, sb.toString(), params,UserClassTypeHour.class);
		if(PropertyUtil.objectNotEmpty(l))
			return l.get(0);
		else
			return null; 
	}

	@Override
	public TrainingClassUser findTrainingClassUser(TrainingClassUser t) {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer(" select tcu.class_user_id \"classUserID\", tcu.class_id \"classID\", tcu.active_state  \"activeState\",  tcu.user_id  \"userID\", "
				+ "tcu.auditing_state \"auditingState\", tcu.attendance_state \"attendanceState\",  tcu.sign_up_type  \"signUpType\", tcu.user_name  \"userName\", "
				+ "tcu.sex_code \"sexCode\", to_char(tcu.birthday,'yyyy-MM-dd') \"birthday\", tcu.mobile_telephone \"mobileTelephone\", tcu.folk_code  \"folkCode\", tcu.duty_remark \"dutyRemark\", "
				+ "tcu.duty_level_code \"dutyLevelCode\", tcu.remark \"remark\", tcu.reject_reason \"rejectReason\",  to_char(ifnull(tcu.training_hours,c.training_hour)) \"trainingHours\", "
				+ "tcu.shiguan  \"shiguan\",  tcu.lock_state  \"lockState\", tcu.examination_Remark \"examinationRemark\", "
				+ "tcu.gb_state \"gbState\", tcu.doubtful \"doubtful\", tcu.examination_result \"examinationResult\" from training_class_user tcu left join training_class c on tcu.class_id = c.class_id"
				+ " where tcu.user_id in (select bu.userid from uum_user@uum_link bu "
				+ "left join uum_department_user@uum_link bdu  on bdu.userid = bu.userid left join uum_department@uum_link bd on bdu.depid = bd.depid "
				+ "where bd.treepath like :path )");
		params.put("path", DeptConstants.XCH_DEPT_PATH+"%");
		List<TrainingClassUser> l=(List<TrainingClassUser>)this.executeFind(QueryStringType.SQL, sb.toString(), params,TrainingClassUser.class);
		if(PropertyUtil.objectNotEmpty(l))
			return l.get(0);
		else
			return null;
	}

	@Override
	public TrainingClass findTrainingClass(TrainingClass t) {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer(" select tc.class_id \"classID\", tc.class_name \"className\", tc.classname_year \"classnameYear\", "
				+ "tc.class_main_name \"classMainName\", tc.class_num \"classNum\", tc.class_type \"classType\", to_char(tc.training_hour) \"trainingHour\", "
				+ "to_char(tc.begin_date,'yyyy-MM-dd') \"beginDate\", to_char(tc.end_date,'yyyy-MM-dd') \"endDate\", to_char(tc.enter_begin_time,'yyyy-MM-dd') \"enterBeginTime\",  to_char(tc.enter_end_time,'yyyy-MM-dd') \"enterEndTime\", "
				+ "tc.training_dept_name \"trainingDeptName\", tc.sponsor_id \"sponsorID\",  if(tc.sponsor_name='西城区委组织部','西城区',tc.sponsor_name) \"sponsorName\", nvl(tc.publish_state,0) \"publishState\", "
				+ "nvl(tc.open_state,0) \"openState\", tc.active_state \"activeState\", nvl(tc.report_state,0) \"reportState\", nvl(tc.audit_state,0) \"auditState\", "
				+ "to_char(tc.create_date,'yyyy-MM-dd') \"createDate\", nvl(tc.signup_state,0) \"signupState\", nvl(tc.perfect_state,0) \"perfectState\",  nvl(tc.allotdept_state,0) \"allotdeptState\", "
				+ "to_char(tc.assess_start_time,'yyyy-MM-dd') \"assessStartTime\", to_char(tc.assess_end_time,'yyyy-MM-dd') \"assessEndTime\", tc.lock_state \"lockState\",  tc.category_id \"categoryID\", "
				+ "tc.beoff_station \"beoffStation\", tc.create_depname \"createDepname\" from training_class tc where tc.class_id in ( select tcu.class_id "
				+ "from training_class_user tcu where tcu.user_id in ( select to_char(bu.userid) from uum_user@uum_link bu  "
				+ "left join uum_department_user@uum_link bdu on bdu.userid=bu.userid left join uum_department@uum_link bd on bdu.depid=bd.depid "
				+ "where bd.treepath like :path ) ) ");
		params.put("path", DeptConstants.XCH_DEPT_PATH+"%");
		List<TrainingClass> l=(List<TrainingClass>) this.executeFind(QueryStringType.SQL, sb.toString(), params,TrainingClass.class);
		if(PropertyUtil.objectNotEmpty(l))
			return l.get(0);
		else
			return null;
	}
	
	public boolean judgeTrainingClassPxf(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from training_class c where c.class_id = :classId";
			params.put("classId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	public boolean judgeTrainingClass(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from " + SystemConstants.SYSTEM_CODE + "training_class_pxarchives c where c.class_id = :classId";
			params.put("classId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	@Override
	public void addTrainingClassPxf(TrainingClass trainingClass) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("insert into training_class ");
			sb.append(consParam4Insert(params, new String[][] {
				{"class_id", trainingClass.getClassID()},
				{"classname_year", trainingClass.getClassnameYear()},
				{"class_type", trainingClass.getClassType()},
				{"begin_date", trainingClass.getBeginDate()},
				{"end_date", trainingClass.getEndDate()},
				{"lock_state", intToStr(trainingClass.getLockState())},
				{"training_hour", trainingClass.getTrainingHour()},
//				{"training_day", PropertyUtil.objectNotEmpty(trainingClass.getTrainingHour()) ? ((Double)(Double.valueOf(trainingClass.getTrainingHour()) / 8)).intValue() + "" : ""},
				{"active_state", intToStr(trainingClass.getActiveState())},
				{"create_date", trainingClass.getCreateDate()},
				{"assess_start_time", trainingClass.getAssessStartTime()},
				{"assess_end_time", trainingClass.getAssessEndTime()},
				{"training_dept_id", trainingClass.getTrainingDeptId()},
				{"training_dept_name", trainingClass.getTrainingDeptName()},
				{"training_address", trainingClass.getTrainingAddress()},
				{"contact_people", trainingClass.getContactPeople()},
				{"contact_tel", trainingClass.getContactTel()},
				{"phone", trainingClass.getPhone()},
				{"fax", trainingClass.getFax()},
				{"report_state", intToStr(trainingClass.getReportState())},
				{"audit_state", intToStr(trainingClass.getAuditState())},
				{"open_state", intToStr(trainingClass.getOpenState())},
				{"class_name", trainingClass.getClassName()},
				{"class_main_name", trainingClass.getClassMainName()},
//				{"class_num", trainingClass.getClassNum()},
				//去各自系统编号
				{"class_num", trainingClassDao.getTrainingClassNum()},
//				{"create_depname", trainingClass.getCreateDepname()},
				{"signup_state", intToStr(trainingClass.getSignupState())},
				{"category_id", trainingClass.getCategoryID()},
				{"beoff_station", trainingClass.getBeoffStation()},
				{"publish_state", intToStr(trainingClass.getPublishState())},
				{"perfect_state", intToStr(trainingClass.getPerfectState())},
				{"allotdept_state", intToStr(trainingClass.getAllotdeptState())},
				{"sponsor_id", trainingClass.getSponsorID()},
				{"sponsor_name", trainingClass.getSponsorName()},
				{"enter_begin_time", trainingClass.getEnterBeginTime()},
				{"enter_end_time", trainingClass.getEnterEndTime()},
				{"latest_operate_date", trainingClass.getLatestOperateDate()}
			}));
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void addTrainingClass(TrainingClass trainingClass) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			//获得在培训系统中的班级编号
			String classNum=trainingClassDao.getTrainingClassNumByClassID(trainingClass.getClassID());
			StringBuffer sb = new StringBuffer("insert into " + SystemConstants.SYSTEM_CODE + "training_class_pxarchives ");
			sb.append(consParam4Insert(params, new String[][] {
				{"class_id", trainingClass.getClassID()},
				{"classname_year", trainingClass.getClassnameYear()},
				{"class_type", trainingClass.getClassType()},
				{"begin_date", trainingClass.getBeginDate()},
				{"end_date", trainingClass.getEndDate()},
				{"lock_state", intToStr(trainingClass.getLockState())},
				{"training_hour", trainingClass.getTrainingHour()},
				{"active_state", intToStr(trainingClass.getActiveState())},
				{"create_date", trainingClass.getCreateDate()},
				{"assess_start_time", trainingClass.getAssessStartTime()},
				{"assess_end_time", trainingClass.getAssessEndTime()},
				{"training_dept_id", trainingClass.getTrainingDeptId()},
				{"training_dept_name", trainingClass.getTrainingDeptName()},
				{"training_address", trainingClass.getTrainingAddress()},
				{"contact_people", trainingClass.getContactPeople()},
				{"contact_tel", trainingClass.getContactTel()},
				{"phone", trainingClass.getPhone()},
				{"fax", trainingClass.getFax()},
				{"report_state", intToStr(trainingClass.getReportState())},
				{"audit_state", intToStr(trainingClass.getAuditState())},
				{"open_state", intToStr(trainingClass.getOpenState())},
				{"class_name", trainingClass.getClassName()},
				{"class_main_name", trainingClass.getClassMainName()},
//				{"class_num", trainingClass.getClassNum()},
				//去各自系统编号
				{"class_num",classNum==null?trainingClass.getClassNum():classNum },
				{"create_depname", trainingClass.getCreateDepname()},
				{"signup_state", intToStr(trainingClass.getSignupState())},
				{"category_id", trainingClass.getCategoryID()},
				{"beoff_station", trainingClass.getBeoffStation()},
				{"publish_state", intToStr(trainingClass.getPublishState())},
				{"perfect_state", intToStr(trainingClass.getPerfectState())},
				{"allotdept_state", intToStr(trainingClass.getAllotdeptState())},
				{"sponsor_id", trainingClass.getSponsorID()},
				{"sponsor_name", trainingClass.getSponsorName()},
				{"enter_begin_time", trainingClass.getEnterBeginTime()},
				{"enter_end_time", trainingClass.getEnterEndTime()},
				{"latest_operate_date", trainingClass.getLatestOperateDate()}
			}));
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void updateTrainingClassPxf(TrainingClass trainingClass) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update training_class set ");
			sb.append(consParam4Update(params, new String[][] {
				{"classname_year", trainingClass.getClassnameYear()},
				{"class_type", trainingClass.getClassType()},
				{"begin_date", trainingClass.getBeginDate()},
				{"end_date", trainingClass.getEndDate()},
				{"lock_state", intToStr(trainingClass.getLockState())},
				{"training_hour", trainingClass.getTrainingHour()},
				{"active_state", intToStr(trainingClass.getActiveState())},
				{"create_date", trainingClass.getCreateDate()},
				{"assess_start_time", trainingClass.getAssessStartTime()},
				{"assess_end_time", trainingClass.getAssessEndTime()},
				{"training_dept_id", trainingClass.getTrainingDeptId()},
				{"training_dept_name", trainingClass.getTrainingDeptName()},
				{"training_address", trainingClass.getTrainingAddress()},
				{"contact_people", trainingClass.getContactPeople()},
				{"contact_tel", trainingClass.getContactTel()},
				{"phone", trainingClass.getPhone()},
				{"fax", trainingClass.getFax()},
				{"report_state", intToStr(trainingClass.getReportState())},
				{"audit_state", intToStr(trainingClass.getAuditState())},
				{"open_state", intToStr(trainingClass.getOpenState())},
				{"class_name", trainingClass.getClassName()},
				{"class_main_name", trainingClass.getClassMainName()},
//				{"class_num", trainingClass.getClassNum()},
//				{"create_depname", trainingClass.getCreateDepname()},
				{"signup_state", intToStr(trainingClass.getSignupState())},
				{"category_id", trainingClass.getCategoryID()},
				{"beoff_station", trainingClass.getBeoffStation()},
				{"publish_state", intToStr(trainingClass.getPublishState())},
				{"perfect_state", intToStr(trainingClass.getPerfectState())},
				{"allotdept_state", intToStr(trainingClass.getAllotdeptState())},
				{"sponsor_id", trainingClass.getSponsorID()},
				{"sponsor_name", trainingClass.getSponsorName()},
				{"enter_begin_time", trainingClass.getEnterBeginTime()},
				{"enter_end_time", trainingClass.getEnterEndTime()},
				{"latest_operate_date", trainingClass.getLatestOperateDate()}
			}));
			sb.append(" where class_id = :classId");
			params.put("classId", trainingClass.getClassID());
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void updateTrainingClass(TrainingClass trainingClass) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update " + SystemConstants.SYSTEM_CODE + "training_class_pxarchives set ");
			sb.append(consParam4Update(params, new String[][] {
				{"classname_year", trainingClass.getClassnameYear()},
				{"class_type", trainingClass.getClassType()},
				{"begin_date", trainingClass.getBeginDate()},
				{"end_date", trainingClass.getEndDate()},
				{"lock_state", intToStr(trainingClass.getLockState())},
				{"training_hour", trainingClass.getTrainingHour()},
//				{"training_day", PropertyUtil.objectNotEmpty(trainingClass.getTrainingHour()) ? ((Double)(Double.valueOf(trainingClass.getTrainingHour()) / 8)).intValue() + "" : ""},
				{"active_state", intToStr(trainingClass.getActiveState())},
				{"create_date", trainingClass.getCreateDate()},
				{"assess_start_time", trainingClass.getAssessStartTime()},
				{"assess_end_time", trainingClass.getAssessEndTime()},
				{"training_dept_id", trainingClass.getTrainingDeptId()},
				{"training_dept_name", trainingClass.getTrainingDeptName()},
				{"training_address", trainingClass.getTrainingAddress()},
				{"contact_people", trainingClass.getContactPeople()},
				{"contact_tel", trainingClass.getContactTel()},
				{"phone", trainingClass.getPhone()},
				{"fax", trainingClass.getFax()},
				{"report_state", intToStr(trainingClass.getReportState())},
				{"audit_state", intToStr(trainingClass.getAuditState())},
				{"open_state", intToStr(trainingClass.getOpenState())},
				{"class_name", trainingClass.getClassName()},
				{"class_main_name", trainingClass.getClassMainName()},
//				{"class_num", trainingClass.getClassNum()},
				{"create_depname", trainingClass.getCreateDepname()},
				{"signup_state", intToStr(trainingClass.getSignupState())},
				{"category_id", trainingClass.getCategoryID()},
				{"beoff_station", trainingClass.getBeoffStation()},
				{"publish_state", intToStr(trainingClass.getPublishState())},
				{"perfect_state", intToStr(trainingClass.getPerfectState())},
				{"allotdept_state", intToStr(trainingClass.getAllotdeptState())},
				{"sponsor_id", trainingClass.getSponsorID()},
				{"sponsor_name", trainingClass.getSponsorName()},
				{"enter_begin_time", trainingClass.getEnterBeginTime()},
				{"enter_end_time", trainingClass.getEnterEndTime()},
				{"latest_operate_date", trainingClass.getLatestOperateDate()}
			}));
			sb.append(" where class_id = :classId");
			params.put("classId", trainingClass.getClassID());
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public boolean judgeTrainingClassUserPxf(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from training_class_user cu where cu.class_user_id = :classUserId";
			params.put("classUserId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	public boolean judgeTrainingClassUser(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from " + SystemConstants.SYSTEM_CODE + "training_class_user_pxarchives cu where cu.class_user_id = :classUserId";
			params.put("classUserId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	@Override
	public void addTrainingClassUserPxf(TrainingClassUser trainingClassUser) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("insert into training_class_user ");
			sb.append(consParam4Insert(params, new String[][] {
				{"class_user_id", trainingClassUser.getClassUserID()},
				{"training_hours", trainingClassUser.getTrainingHours()},
				{"class_id", trainingClassUser.getClassID()},
				{"auditing_state", intToStr(trainingClassUser.getAuditingState())},
				{"user_id", trainingClassUser.getUserID()},
				{"sign_up_type", trainingClassUser.getSignUpType()},
//				{"shiguan", intToStr(trainingClassUser.getShiguan())},
				{"attendance_state", intToStr(trainingClassUser.getAttendanceState())},
//				{"lock_state", intToStr(trainingClassUser.getLockState())},
//				{"doubtful", trainingClassUser.getDoubtful()},
//				{"gb_state", intToStr(trainingClassUser.getGbState())},
				{"user_name", trainingClassUser.getUserName()},
				{"birthday", trainingClassUser.getBirthday()},
				{"duty_remark", trainingClassUser.getDutyRemark()},
				{"mobile_telephone", trainingClassUser.getMobileTelephone()},
				{"remark", trainingClassUser.getRemark()},
				{"folk_code", trainingClassUser.getFolkCode()},
				{"sex_code", trainingClassUser.getSexCode()},
				{"active_state", intToStr(trainingClassUser.getActiveState())},
				{"reject_reason", trainingClassUser.getRejectReason()}
			}));
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void addTrainingClassUser(TrainingClassUser trainingClassUser) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("insert into " + SystemConstants.SYSTEM_CODE + "training_class_user_pxarchives ");
			sb.append(consParam4Insert(params, new String[][] {
				{"class_user_id", trainingClassUser.getClassUserID()},
				{"training_hours", trainingClassUser.getTrainingHours()},
				{"class_id", trainingClassUser.getClassID()},
				{"auditing_state", intToStr(trainingClassUser.getAuditingState())},
				{"user_id", trainingClassUser.getUserID()},
				{"sign_up_type", trainingClassUser.getSignUpType()},
				{"shiguan", intToStr(trainingClassUser.getShiguan())},
				{"attendance_state", intToStr(trainingClassUser.getAttendanceState())},
				{"lock_state", intToStr(trainingClassUser.getLockState())},
				{"doubtful", trainingClassUser.getDoubtful()},
				{"gb_state", intToStr(trainingClassUser.getGbState())},
				{"user_name", trainingClassUser.getUserName()},
				{"birthday", trainingClassUser.getBirthday()},
				{"duty_remark", trainingClassUser.getDutyRemark()},
				{"mobile_telephone", trainingClassUser.getMobileTelephone()},
				{"remark", trainingClassUser.getRemark()},
				{"folk_code", trainingClassUser.getFolkCode()},
				{"sex_code", trainingClassUser.getSexCode()},
				{"active_state", intToStr(trainingClassUser.getActiveState())},
				{"reject_reason", trainingClassUser.getRejectReason()}
			}));
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public String intToStr(Integer i) {
		return i != null ? i.toString() : "";
	}
	
	@Override
	public void updateTrainingClassUserPxf(TrainingClassUser trainingClassUser) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update training_class_user set ");
			sb.append(consParam4Update(params, new String[][] {
				{"training_hours", trainingClassUser.getTrainingHours()},
				{"class_id", trainingClassUser.getClassID()},
				{"auditing_state", intToStr(trainingClassUser.getAuditingState())},
				{"user_id", trainingClassUser.getUserID()},
				{"sign_up_type", trainingClassUser.getSignUpType()},
//				{"shiguan", intToStr(trainingClassUser.getShiguan())},
				{"attendance_state", intToStr(trainingClassUser.getAttendanceState())},
//				{"lock_state", intToStr(trainingClassUser.getLockState())},
//				{"doubtful", trainingClassUser.getDoubtful()},
//				{"gb_state", intToStr(trainingClassUser.getGbState())},
				{"user_name", trainingClassUser.getUserName()},
				{"birthday", trainingClassUser.getBirthday()},
				{"duty_remark", trainingClassUser.getDutyRemark()},
				{"mobile_telephone", trainingClassUser.getMobileTelephone()},
				{"remark", trainingClassUser.getRemark()},
				{"folk_code", trainingClassUser.getFolkCode()},
				{"sex_code", trainingClassUser.getSexCode()},
				{"active_state", intToStr(trainingClassUser.getActiveState())},
				{"reject_reason", trainingClassUser.getRejectReason()}
			}));
			sb.append(" where class_user_id = :classUserId");
			params.put("classUserId", trainingClassUser.getClassUserID());
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void updateTrainingClassUser(TrainingClassUser trainingClassUser) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update " + SystemConstants.SYSTEM_CODE + "training_class_user_pxarchives set ");
			sb.append(consParam4Update(params, new String[][] {
				{"training_hours", trainingClassUser.getTrainingHours()},
				{"class_id", trainingClassUser.getClassID()},
				{"auditing_state", intToStr(trainingClassUser.getAuditingState())},
				{"user_id", trainingClassUser.getUserID()},
				{"sign_up_type", trainingClassUser.getSignUpType()},
				{"shiguan", intToStr(trainingClassUser.getShiguan())},
				{"attendance_state", intToStr(trainingClassUser.getAttendanceState())},
				{"lock_state", intToStr(trainingClassUser.getLockState())},
				{"doubtful", trainingClassUser.getDoubtful()},
				{"gb_state", intToStr(trainingClassUser.getGbState())},
				{"user_name", trainingClassUser.getUserName()},
				{"birthday", trainingClassUser.getBirthday()},
				{"duty_remark", trainingClassUser.getDutyRemark()},
				{"mobile_telephone", trainingClassUser.getMobileTelephone()},
				{"remark", trainingClassUser.getRemark()},
				{"folk_code", trainingClassUser.getFolkCode()},
				{"sex_code", trainingClassUser.getSexCode()},
				{"active_state", intToStr(trainingClassUser.getActiveState())},
				{"reject_reason", trainingClassUser.getRejectReason()}
			}));
			sb.append(" where class_user_id = :classUserId");
			params.put("classUserId", trainingClassUser.getClassUserID());
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public boolean judgeUserClassTypeHour(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from user_class_type_hour h where h.id = :id";
			params.put("id", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	@Override
	public void addUserClassTypeHour(UserClassTypeHour userClassTypeHour) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("insert into user_class_type_hour ");
			sb.append(consParam4Insert(params, new String[][] {
				{"id", userClassTypeHour.getID().toString()},
				{"hour_year", userClassTypeHour.getHourYear().toString()},
				{"hours", userClassTypeHour.getHours().toString()},
				{"zx_hour", userClassTypeHour.getZxHour().toString()},
				{"zxzs_hour", userClassTypeHour.getZxZsHour().toString()},
				{"ztb_hour", userClassTypeHour.getZtbHour().toString()},
				{"zhutb_hour", userClassTypeHour.getZhutbHour().toString()},
				{"zhongydx_hour", userClassTypeHour.getZhongydxHour().toString()},
				{"jwpx_hour", userClassTypeHour.getJwpxHour().toString()},
				{"qit_hour", userClassTypeHour.getQitHour().toString()},
				{"user_id", userClassTypeHour.getUserID().toString()},
				{"create_date", userClassTypeHour.getCreateDateStr()}
			}));
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void updateUserClassTypeHour(UserClassTypeHour userClassTypeHour) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update user_class_type_hour set ");
			sb.append(consParam4Update(params, new String[][] {
				{"hour_year", userClassTypeHour.getHourYear().toString()},
				{"hours", userClassTypeHour.getHours().toString()},
				{"zx_hour", userClassTypeHour.getZxHour().toString()},
				{"zxzs_hour", userClassTypeHour.getZxZsHour().toString()},
				{"ztb_hour", userClassTypeHour.getZtbHour().toString()},
				{"zhutb_hour", userClassTypeHour.getZhutbHour().toString()},
				{"zhongydx_hour", userClassTypeHour.getZhongydxHour().toString()},
				{"jwpx_hour", userClassTypeHour.getJwpxHour().toString()},
				{"qit_hour", userClassTypeHour.getQitHour().toString()},
				{"user_id", userClassTypeHour.getUserID().toString()},
				{"create_date", userClassTypeHour.getCreateDateStr()}
			}));
			sb.append(" where id = :id");
			params.put("id", userClassTypeHour.getID());
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public boolean judgeClassScopeDept(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from class_scope_department d where d.scope_dept_id = :scopeDeptId";
			params.put("scopeDeptId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}

	@Override
	public boolean judgeClassSignUpDept(String... strings) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			String sql = "select count(1) from class_sign_up_department d where d.sign_up_dept_id = :signUpDeptId";
			params.put("signUpDeptId", strings[0]);
			List<?> list = this.executeFind(QueryStringType.SQL, sql, params);
			if (PropertyUtil.objectNotEmpty(list) && PropertyUtil.objectNotEmpty(list.get(0)))
				return !list.get(0).toString().equals("0");
		} catch (Exception e) {
			e.printStackTrace();
		}
		return false;
	}
	
	@Override
	public void cleanClassScopeDept(String classId) {
		try {
			String cleanSql = "delete from class_scope_department where class_id = :classId";
			Map<String, Object> cleanParams = new HashMap<String, Object>();
			cleanParams.put("classId", classId);
			this.executeUpdate(QueryStringType.SQL, cleanSql, cleanParams);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public void addClassScopeDept(ClassScopeDept classScopeDept) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("insert into class_scope_department ");
			sb.append(consParam4Insert(params, new String[][] {
				{"scope_dept_id", classScopeDept.getScopeDeptId()},
				{"class_id", classScopeDept.getClassId()},
				{"dept_id", classScopeDept.getDeptId()},
				{"active_state", intToStr(classScopeDept.getActiveState())},
				{"create_date", classScopeDept.getCreateDate()}
			}));
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void updateClassScopeDept(ClassScopeDept classScopeDept) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update class_scope_department set ");
			sb.append(consParam4Update(params, new String[][] {
				{"class_id", classScopeDept.getClassId()},
				{"dept_id", classScopeDept.getDeptId()},
				{"active_state", intToStr(classScopeDept.getActiveState())},
				{"create_date", classScopeDept.getCreateDate()}
			}));
			sb.append(" where scope_dept_id = :scopeDeptId");
			params.put("scopeDeptId", classScopeDept.getScopeDeptId());
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void cleanClassSignUpDept(String classId) {
		try {
			String cleanSql = "delete from class_sign_up_department where class_id = :classId";
			Map<String, Object> cleanParams = new HashMap<String, Object>();
			cleanParams.put("classId", classId);
			this.executeUpdate(QueryStringType.SQL, cleanSql, cleanParams);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void addClassSignUpDept(ClassSignUpDept classSignUpDept) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("insert into class_sign_up_department ");
			sb.append(consParam4Insert(params, new String[][] {
				{"sign_up_dept_id", classSignUpDept.getSignUpDeptId()},
				{"class_id", classSignUpDept.getClassId()},
				{"dept_id", classSignUpDept.getDeptId()},
				{"active_state", intToStr(classSignUpDept.getActiveState())},
				{"create_date", classSignUpDept.getCreateDate()}
			}));
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	@Override
	public void updateClassSignUpDept(ClassSignUpDept classSignUpDept) {
		try {
			Map<String, Object> params = new HashMap<String, Object>();
			StringBuffer sb = new StringBuffer("update class_sign_up_department ");
			sb.append(consParam4Update(params, new String[][] {
				{"class_id", classSignUpDept.getClassId()},
				{"dept_id", classSignUpDept.getDeptId()},
				{"active_state", intToStr(classSignUpDept.getActiveState())},
				{"create_date", classSignUpDept.getCreateDate()}
			}));
			sb.append(" where sign_up_dept_id = :signUpDeptId");
			params.put("signUpDeptId", classSignUpDept.getSignUpDeptId());
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	@Override
	public List<String> findAnnulUserIds() {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer("select user_id from annul_user");
		return (List<String>) this.executeFind(QueryStringType.SQL, sb.toString(), params);
	}

	@Override
	public void cleanAnnulUser() {
		Map<String,Object> params=new HashMap<String,Object>();
		StringBuffer sb=new StringBuffer("delete from annul_user ");
		this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
	}
	
	@Override
	public void deleteAnnulUserPxfByIds(List<String> annulUserIds) {
		if (PropertyUtil.objectNotEmpty(annulUserIds) && annulUserIds.size() > 0) {
			Map<String,Object> params=new HashMap<String,Object>();
			StringBuffer sb=new StringBuffer("delete from training_class_user where user_id in (:userIds)");
			params.put("userIds", annulUserIds);
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		}
	}
	
	@Override
	public void deleteAnnulUserByIds(List<String> annulUserIds) {
		if (PropertyUtil.objectNotEmpty(annulUserIds) && annulUserIds.size() > 0) {
			Map<String,Object> params=new HashMap<String,Object>();
			StringBuffer sb=new StringBuffer("delete from " + SystemConstants.SYSTEM_CODE + "training_class_user_pxarchives where user_id in (:userIds)");
			params.put("userIds", annulUserIds);
			this.executeUpdate(QueryStringType.SQL, sb.toString(), params);
		}
	}
}
